from dateutil import parser
from datetime import datetime
from collections import defaultdict
from openpyxl.styles import Alignment
from . import myexcel
from config import *
from sql import sqlseverDB
from collections import Counter
import hashlib,concurrent.futures
import string,random,copy,urllib.parse
import openpyxl,re,time,math,os,requests,json

ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

def contains_word(title, words):

    for word in words:
        if word.lower() in title.lower():
            return [word]

    # 判断交集是否为空
    return False

def create_dj2(fpath:str):
    
    if fpath.endswith('.xlsx'):
        sheet_datas=myexcel.getDictDatasByEecel2(fpath)
        datas=[]
        for r in sheet_datas:
            datas.append([1,r])
        nwb=openpyxl.Workbook()
        nws=nwb[nwb.sheetnames[0]]
        nws.append(['关键词','最低价格','最高价格','店铺ID','产品名字','产品链接'])
        xunhcot=0
        while True:
            if len(datas)==0:
                break
            ndatas=[]
            for dj,rdata in datas:
                mbdada=['' for col in range(nws.max_column)]
                mbdada[0]=rdata['关键词']
                mbdada[1]=rdata.get('最低价')
                mbdada[2]=rdata.get('最低价')
                mbdada[3]=rdata['商家名字']
                mbdada[4]=''
                mbdada[5]=rdata['产品链接']

                if dj>0:
                    nws.append(mbdada)

                if dj-1>0:
                    ndatas.append([dj-1,rdata])
            
            datas=ndatas
            xunhcot+=1
            print(f'第{xunhcot}圈生成,剩余:{len(datas)}')
        time_local = time.localtime(time.time())
        dt = time.strftime("%Y-%m-%d", time_local)
        npath=f'点击任务_卡价格_{dt}.xlsx'
        nwb.save(npath)
        print(f'生成点击表完毕《{npath}》')

def getTimeStr():

    time_local = time.localtime(time.time())
    dt = time.strftime("%Y-%m-%d_%H-%M-%S", time_local)
    return dt

def getTimeStr2(ct):

    time_local = time.localtime(ct)
    dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
    return dt

def getTimeTit():

    time_local = time.localtime(time.time())
    dt = time.strftime("%Y-%m-%d_%H-%M-%S", time_local)
    return dt

def trydosql(cms,sqlstr,params=None,type=1,force=0):

    while True:
        try:
            if type==1:
                res=cms.ExecQuery(sqlstr,params)
            elif type==2:
                res=cms.ExecNoQuery(sqlstr,params)
                if not res and force==1:
                    time.sleep(1)
                    continue
            elif type==3:
                res=cms.ExecQuerydict(sqlstr,params)
            
            return res
        except Exception as e:
            pass
        time.sleep(1)

def sqlToExcel(fnameqz,cms,sqlstr,params=None,dbpmap=None):
    rs=trydosql(cms,sqlstr,params,3)

    wb=openpyxl.Workbook()
    ws=wb[wb.sheetnames[0]]

    ws.append(list(rs[0].keys()))
    if dbpmap:
        ws.append([dbpmap.get(k) if dbpmap.get(k) else k for k in rs[0].keys()])
    for r in rs:
        ws.append(list(r.values()))

            
    for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
        for cell in row:
            cell.alignment = Alignment(horizontal='left', vertical='center')

    current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
    fname=f'{fnameqz}_{current_time_str}.xlsx'
    wb.save(fname)
    return fname

def crate_excel(fnameqz,rs,dbpmap=None):

    if len(rs)>0:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        if not dbpmap:
            ws.append(list(rs[0].keys()))
            for r in rs:
                ws.append(list(r.values()))
            for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
                for cell in row:
                    cell.alignment = Alignment(horizontal='left', vertical='center')

        else:
            curks=list(rs[0].keys())
            ws.append(curks)
            ws.append([dbpmap.get(kkk) if dbpmap.get(kkk) else kkk  for kkk in curks])

            for r in rs:
                ws.append(list(r.values()))
                
            for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
                for cell in row:
                    cell.alignment = Alignment(horizontal='left', vertical='center')

        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        fname=f'{fnameqz}_{current_time_str}.xlsx'
        wb.save(fname)
        return fname
    else:
        return 0

def excel_to_sql(tb_name,excel_path,wzd):
    ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
    def updata(cr:int,pitem:dict):
        para=list(pitem.values())+[pitem[wzd]]
        zdstr=','.join([f'{zd}=?' for zd in pitem.keys()])
        sqlstr=f'update {tb_name} set {zdstr} where {wzd}=?'
        aff= ms.ExecNoQuery(sqlstr,para)

        return cr,pitem[wzd],aff

    pdatas=myexcel.getDictDatasByEecel2(excel_path)
    taskcount=len(pdatas)
    pdatas=pdatas[1:]
    suc=0
    fail=0
    with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
        futures=[]
        row=3
        for pdata in pdatas:

            futures.append(executor.submit(updata,row,pdata))
            row+=1

        for future in concurrent.futures.as_completed(futures):
            try:
                res_row,res_mid,res_aff = future.result()
                taskcount-=1
                print(f'''第 {res_row} 行,{wzd}:{res_mid}, => 更新状态:{res_aff},剩余队列:{taskcount}''')
                if res_aff>0:
                    suc+=1
                else:
                    fail+=1
            except Exception as exc:
                print(f"线程出错: {exc}")
    
    return suc,fail

def convert_to_timestamp(time_str):
    # 使用 dateutil.parser 解析时间字符串
    try:
        dt = parser.parse(time_str)
        
        # 转换为时间戳
        timestamp = time.mktime(dt.timetuple())

        return int(timestamp)
    except Exception as e:
        return -1

def isContain(kw,tit):

    glwords=[re.escape(item) for item in kw.split()]
    flag0=True
    if tit and isinstance(tit,str):
        for gkw in glwords:
            if not re.search(r'\b{}\b'.format(gkw),tit,re.I):
                flag0=False
                break
        return flag0
    
    else:
        return False

def get_config(key):

    with open('config.json','r',encoding='utf-8') as f:
        jsdata=json.load(f)
    
    return jsdata.get(key)

def get_allconfig():

    with open('config.json','r',encoding='utf-8') as f:
        jsdata=json.load(f)
    
    return jsdata

def update_config(updata:dict):

    with open('config.json','r',encoding='utf-8') as f:
        jsdata=json.load(f)
    
    for k,v in updata.items():

        jsdata[k]=v

    with open('config.json','w',encoding='utf-8') as f:
        jsdata=json.dump(jsdata,f,ensure_ascii=False,indent=4)

def get_random_code(length=8):
    characters = string.ascii_letters + string.digits
    random_code = ''.join(random.choice(characters) for _ in range(length))
    return random_code

def generate_sku_id(length=10):
    characters = string.ascii_uppercase + string.digits
    sku_id = ''.join(random.choices(characters, k=length))
    return sku_id

def getsmtpro(rp):
    # 处理 sku1, sku2 和 pdec
    for item in rp:
        for skuitem in ['sku1', 'sku2']:
            if item[skuitem]:
                if len(item[skuitem]) > 20:
                    item[skuitem] = item[skuitem][:18]
        if not item['pdec']:
            item['pdec'] = item['yntitle']

        if item['pdec'] and len(item['pdec']) > 2500:
            item['pdec'] = item['pdec'][:2500]



    # 处理 sku1 和 sku2 重复的情况
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id, sku1, sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id, sku1, sku2)]}"
                count_dict[(item_id, sku1, sku2)] += 1
            else:
                count_dict[(item_id, sku1, sku2)] = 1


    rp_dict = {}
    for r in rp:
        item_id = r['itemid']
        if rp_dict.get(item_id):
            rp_dict[item_id].append(r)
        else:
            rp_dict[item_id] = [r]

    final_result = []
    for item_id, records in rp_dict.items():
        grouped_by_sku1 = defaultdict(list)
        cur_sku2s=defaultdict(set)
        for record in records:
            grouped_by_sku1[record['sku1']].append(record)
            if not record['sku2']:
                record['sku2']=''
            cur_sku2s[record['sku1']].add(record['sku2'])
        current_count = 0
        item_group = []
        #print(cur_sku2s.values())
        set_counts = Counter(tuple(sorted(s)) for s in cur_sku2s.values())
        most_common_set, count = set_counts.most_common(1)[0]
        for sku1, sku_records in grouped_by_sku1.items():
            if cur_sku2s[sku1]==set(most_common_set):
                if current_count + len(sku_records) <= 80:
                    item_group.extend(sku_records)
                    current_count += len(sku_records)
                else:
                    break
        final_result.append(item_group)
    #final_result=list(rp_dict.values())
    return final_result

def get_pod_pro(rp,skus):
    # 处理 sku1, sku2 和 pdec
    rp_dict = {}
    for item in rp:
        item_id = item['itemid']

        if not item['pdec']:
            item['pdec'] = item['yntitle']
        if item['pdec'] and len(item['pdec']) > 2500:
            item['pdec'] = item['pdec'][:2500]

        for sku1,sku2 in skus:
            new_pro=copy.copy(item)
            if len(sku1) > 20:
                sku1=sku1[:18]
            if len(sku2)>20:
                sku2=sku2[:18]
            new_pro['skuid']=generate_sku_id()
            new_pro['sku1']=sku1
            new_pro['sku2']=sku2

            if rp_dict.get(item_id):
                rp_dict[item_id].append(new_pro)
            else:
                rp_dict[item_id] = [new_pro]

    final_result=list(rp_dict.values())
    return final_result


def dc_exceldata(pros,mbid,everycount,filecount,phl,pbs,dcmr,smtys,zjys,fixed_price=None):

    #print(f'导出份数:{filecount},每份最少量:{everycount}')
    rmb=ms.ExecQuery('select dfile from [mb] where did=?',(mbid,))
    jdpath=rmb[0][0]
    jdpaths=jdpath.split('\\')
    if not os.path.exists('官方模版表'):
        os.mkdir('官方模版表')

    mbpath=f'官方模版表/{jdpaths[-1]}'

    downpath=f'http://198.12.127.43/{jdpaths[-2]}/{jdpaths[-1]}'
    print(f'正在从服务器下载模版id:{mbid} 文件...')
    while True:
        try:
            cot=requests.get(downpath).content
            break
        except Exception as e:
            pass
    with open(mbpath,'wb') as f:
        f.write(cot)
    rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))

    xhs=math.ceil(len(pros)/everycount)

    for j in range(xhs):
        wb=openpyxl.load_workbook(mbpath)
        ws_name='Template'
        row=7
        for sname in wb.sheetnames:
            if 'template' in sname.lower():
                ws_name=sname
                break
            elif 'tshirts' in sname.lower():
                ws_name=sname
                row=5
                break
        ws=wb[ws_name]
        
        lowind=j*everycount
        maxind=(j+1)*everycount if (j+1)*everycount<len(pros) else len(pros)
        curprocount=maxind-lowind
        for item in pros[lowind:maxind]:
            for pro in item:
                if not pro['pdec']:
                    pro['pdec']=pro['yntitle']
                if pro['pdec'] and len(pro['pdec'].strip())<20:
                    pro['pdec']=pro['yntitle']

                allimgs=[pro['image']]
                if pro['images']:
                    try:
                        allimgs.extend(json.loads(pro['images']))
                    except Exception as e:
                        pass
                if fixed_price:
                    pro['price']=fixed_price
                    pro['skuPirce']=fixed_price
                else:
                    pro['price']=int(phl*pro['price']*pbs)
                    pro['skuPirce']=int(phl*pro['skuPirce']*pbs)
                    
                rol=1
                try:
                    for ry in rys:
                        mbcol=ry[1]
                        smtcol=ry[2]
                        gd=ry[4]

                        if smtcol=='SKU图片网址' and fixed_price:
                            rol+=1
                            continue
                        
                        if '无' not in smtcol and smtcol:
                            if smtcol=='SKU名称':
                                if zjys.get(mbcol):
                                    ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                    if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                        ws.cell(row=row,column=rol).value='style'

                            elif smtcol=='主图网址':
                                if len(allimgs)>0:
                                    ws.cell(row=row,column=rol).value=allimgs.pop(0)
                            elif smtcol=='SKU图片网址':
                                if not pro['skuImg']:
                                    ws.cell(row=row,column=rol).value=pro['image']
                                else:
                                    ws.cell(row=row,column=rol).value=pro['skuImg']
                            else:
                                ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                        
                        else:
                            ws.cell(row=row,column=rol).value=gd
                            if gd=='size' and  not pro['sku2']:
                                ws.cell(row=row,column=rol).value=''

                        rol+=1
                    row+=1
                except Exception as e:
                    pass

        wb.save(f'{dcmr}/{mbid}-{curprocount}-{j+1}.xlsx')
        print(f'成功保存：{mbid}-{curprocount}-{j+1}.xlsx')
        if j+1==filecount:
            break

def dc_lazada(pros,mbid,everycount,filecount,phl,pbs,dcmr,smtys,zjys,fixed_price=None):

    #print(f'导出份数:{filecount},每份最少量:{everycount}')

    mbpath=f'官方模版表/ttt.xlsx'


    rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))

    xhs=math.ceil(len(pros)/everycount)

    for j in range(xhs):
        wb=openpyxl.load_workbook(mbpath)
        ws_name='TShirts'
        row=5
        ws=wb[ws_name]
        
        lowind=j*everycount
        maxind=(j+1)*everycount if (j+1)*everycount<len(pros) else len(pros)
        curprocount=maxind-lowind
        for item in pros[lowind:maxind]:
            for pro in item:
                if not pro['pdec']:
                    pro['pdec']=pro['pname']
                if pro['pdec'] and len(pro['pdec'].strip())<20:
                    pro['pdec']=pro['pname']

                allimgs=[pro['image']]
                if pro['images']:
                    try:
                        allimgs.extend(json.loads(pro['images']))
                    except Exception as e:
                        pass
                if fixed_price:
                    pro['price']=fixed_price
                    pro['skuPirce']=fixed_price
                else:
                    pro['price']=int(phl*pro['price']*pbs)
                    pro['skuPirce']=int(phl*pro['skuPirce']*pbs)
                    
                rol=1
                try:
                    for ry in rys:
                        mbcol=ry[1]
                        smtcol=ry[2]
                        gd=ry[4]

                        if smtcol=='SKU图片网址' and fixed_price:
                            rol+=1
                            continue
                        
                        if '无' not in smtcol and smtcol:
                            if smtcol=='SKU名称':
                                if zjys.get(mbcol):
                                    ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                    if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                        ws.cell(row=row,column=rol).value='style'

                            elif smtcol=='主图网址':
                                if len(allimgs)>0:
                                    ws.cell(row=row,column=rol).value=allimgs.pop(0)
                            elif smtcol=='SKU图片网址':
                                if not pro['skuImg']:
                                    ws.cell(row=row,column=rol).value=pro['image']
                                else:
                                    ws.cell(row=row,column=rol).value=pro['skuImg']
                            else:
                                ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                        
                        else:
                            ws.cell(row=row,column=rol).value=gd
                            if gd=='size' and  not pro['sku2']:
                                ws.cell(row=row,column=rol).value=''

                        rol+=1
                    row+=1
                except Exception as e:
                    pass

        wb.save(f'{dcmr}/{mbid}-{curprocount}-{j+1}.xlsx')
        print(f'成功保存：{mbid}-{curprocount}-{j+1}.xlsx')
        if j+1==filecount:
            break

def second_dc_exceldata(pros,mbid,dcmr,smtys,zjys):

    rmb=ms.ExecQuery('select dfile from [mb] where did=?',(mbid,))
    jdpath=rmb[0][0]
    jdpaths=jdpath.split('\\')
    if not os.path.exists('官方模版表'):
        os.mkdir('官方模版表')

    mbpath=f'官方模版表/{jdpaths[-1]}'

    downpath=f'http://198.12.127.43/{jdpaths[-2]}/{jdpaths[-1]}'
    print(f'正在从服务器下载模版id:{mbid} 文件...')
    while True:
        try:
            cot=requests.get(downpath).content
            break
        except Exception as e:
            pass
    with open(mbpath,'wb') as f:
        f.write(cot)
    rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))

    wb=openpyxl.load_workbook(mbpath)
    ws=wb['Template']
    row=7

    curprocount=len(pros)
    for item in pros:
        npmb=[pttt['NewPrice_shopeeid'] for pttt in item if pttt['NewPrice_shopeeid']][0]
        for pro in item:
            
            allimgs=[pro['image']]
            if pro['images']:
                try:
                    allimgs.extend(json.loads(pro['images']))
                except Exception as e:
                    pass

            rol=1
            try:
                for ry in rys:
                    mbcol=ry[1]
                    smtcol=ry[2]
                    gd=ry[4]

                    if mbcol=='Stock' and not pro['NewPrice_shopeeid']:
                        ws.cell(row=row,column=rol).value=0
                        rol+=1
                        continue

                    if mbcol=='Price' and not pro['NewPrice_shopeeid']:
                        ws.cell(row=row,column=rol).value=npmb
                        rol+=1
                        continue
                    
                    if '无' not in smtcol and smtcol:
                        if smtcol=='SKU名称':
                            if zjys.get(mbcol):
                                ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                    ws.cell(row=row,column=rol).value='style'

                        elif smtcol=='主图网址':
                            if len(allimgs)>0:
                                ws.cell(row=row,column=rol).value=allimgs.pop(0)
                        
                        elif smtcol=='SKU图片网址':
                            if not pro['skuImg']:
                                ws.cell(row=row,column=rol).value=pro['image']
                            else:
                                ws.cell(row=row,column=rol).value=pro['skuImg']
                        else:
                            ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                    
                    else:
                        ws.cell(row=row,column=rol).value=gd
                        if gd=='size' and  not pro['sku2']:
                            ws.cell(row=row,column=rol).value=''

                    rol+=1
                row+=1
            except Exception as e:
                pass

    wb.save(f'{dcmr}/{mbid}-{curprocount}.xlsx')
    print(f'成功保存：{mbid}-{curprocount}.xlsx')

def get_days_diff_from_timestamps(timestamp1, timestamp2):  
    """  
    使用datetime模块计算两个时间戳之间的天数差  
  
    参数:  
    timestamp1 (int): 第一个时间戳（秒为单位）  
    timestamp2 (int): 第二个时间戳（秒为单位）  
  
    返回:  
    int: 两个时间戳之间的天数差  
    """  
    # 将时间戳转换为datetime对象  
    dt1 = datetime.fromtimestamp(timestamp1)  
    dt2 = datetime.fromtimestamp(timestamp2)  
  
    # 计算时间差  
    delta = dt2 - dt1  
  
    # 提取天数差  
    days_diff = delta.days  
  
    return days_diff

def replace_first_occurrence(lst, old_value, new_value):  
    try:  
        index = lst.index(old_value)  
        lst[index] = new_value  
    except ValueError:  
        print(f"Value '{old_value}' not found in the list.")  
    return lst

def md5_encrypt(text):
    # 创建一个 md5 对象
    md5 = hashlib.md5()
    
    # 更新 md5 对象，注意需要编码成 bytes
    md5.update(text.encode('utf-8'))
    
    # 获取加密后的十六进制表示的字符串
    return md5.hexdigest()


def add_query_parameters(base_url, params):
    """
    添加查询参数到链接中，保留原始查询参数。

    Args:
        base_url (str): 基本链接。
        params (dict): 包含参数名称和值的字典。

    Returns:
        str: 包含查询参数的新链接。
    """
    # 解析基本链接以获取其查询字符串部分
    parsed_url = urllib.parse.urlparse(base_url)

    # 解析现有的查询参数
    existing_query_params = urllib.parse.parse_qs(parsed_url.query)

    # 将新参数添加到现有参数中
    existing_query_params.update(params)

    # 构建新的查询字符串
    new_query_string = urllib.parse.urlencode(existing_query_params, doseq=True)

    # 用新的查询字符串替换现有的查询字符串
    new_parsed_url = parsed_url._replace(query=new_query_string)

    # 从新的解析链接中获取最终的链接字符串
    new_url = urllib.parse.urlunparse(new_parsed_url)

    return new_url